package com.yx.system.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.yx.common.annotation.AuthIgnore2;
import com.yx.common.utils.PageUtils;
import com.yx.common.utils.Query;
import com.yx.common.utils.R;
import com.yx.modules.api.annotation.AuthIgnore;
import com.yx.modules.api.controller.FileUpLoadController;
import com.yx.modules.api.entity.TbRewardRecord;
import com.yx.modules.api.entity.TbUser;
import com.yx.modules.api.service.TbRewardRecordService;
import com.yx.modules.api.service.TbUserService;
import com.yx.pc.api.Dto.TbRewardStatisticsDto;
import com.yx.pc.api.Dto.TbSaleStatisticsDto;
import com.yx.system.dto.BookStatisticsDto;
import com.yx.system.entity.TdBook;
import com.yx.system.entity.TdCategory;
import com.yx.system.entity.TdOrder;
import com.yx.system.service.TdBookService;
import com.yx.system.service.TdCategoryService;
import com.yx.system.service.impl.TdOrderServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 后台管理统计
 * Created by wuye on 2017/12/13.
 */
@RestController
@RequestMapping("/sys/statis")
public class SysStatisticsController {

    @Autowired
    private TdOrderServiceImpl tdOrderService;

    @Autowired
    private TdBookService tdBookService;

    @Autowired
    private TbUserService tbUserService;

    @Autowired
    private TdCategoryService tdCategoryService;

    @Autowired
    private TbRewardRecordService tbRewardRecordService;

    @Autowired
    private FileUpLoadController fileUpLoadController;

    //查询月销售排行前五的小说
    @RequestMapping("/findBookTop")
    @RequiresPermissions("sys:statis:findBookTop")
    public R findBookTop() {
        Map<String, Object> result = new HashMap<>();
        //查询月销售榜
        List<TdBook> tdBookListSell = tdBookService.queryByDay();
        result.put("tdBookListSellTop", tdBookListSell);
        //会员男女统计
        Map<String, Object> map = new HashMap<>();
        List<TbUser> tbUserList = tbUserService.queryList(map);
        int nan = 0;
        int nv = 0;
        for (int i = 0; i < tbUserList.size(); i++) {
            if (tbUserList.get(i).getSex()) {
                nan += 1;
            } else {
                nv += 1;
            }
        }
        result.put("nan", nan);
        result.put("nv", nv);
        //累计销售榜
        map.put("rewardMoney", "1");
        List<TdBook> tdBookList = tdBookService.queryList(map);
        result.put("tdBookList", tdBookList);
        //分类统计
        Map<String, Integer> categoryList = new HashMap<>();
        List<TdCategory> tdCategoryList = tdCategoryService.queryList(map);
        for (int i = 0; i < tdCategoryList.size(); i++) {
            map.put("categoryId", tdCategoryList.get(i).getId());
            List<TdBook> tdBookList1 = tdBookService.queryList(map);
            categoryList.put(tdCategoryList.get(i).getCategoryyname(), tdBookList1.size());
        }
        result.put("category", categoryList);
        return R.ok().put("result",result);
    }

    /*
    * 根据时间查询打赏统计
    */
    @AuthIgnore2
    @RequestMapping("getRewardStatistics")
    public R rewardStatistics(@RequestParam Map<String, Object> params){
        String date = String.valueOf(params.get("date"));
        String[] flag = date.split(":");
        String year = flag[0];
        String month = flag[1];
        String day = flag[2];
        params.remove("date");
        params.put("rewardMoney","1");
        //注入图书查询列表数据
        Query query = new Query(params);
        //查询出图书
        List<TdBook> tdBooks = tdBookService.queryList(query);
        int total = tdBookService.queryTotal(query);
        //初始化一个数据传输集合
        List<TbRewardStatisticsDto> tbRewardStatisticsDtos = new ArrayList<>();
        //取出图书id集合
        Integer[] ids = new Integer[tdBooks.size()];
        for (int i = 0; i < tdBooks.size(); i++) {
            ids[i] = tdBooks.get(i).getId();
        }
        //传入数据
        for (TdBook tdBook:tdBooks){
            TbRewardStatisticsDto tbRewardStatisticsDto = new TbRewardStatisticsDto();
            tbRewardStatisticsDto.setBookId(tdBook.getId());
            tbRewardStatisticsDto.setBookName(tdBook.getBookname());
            tbRewardStatisticsDto.setTotalReward(0);
            tbRewardStatisticsDto.setYearReward(0);
            tbRewardStatisticsDto.setMonthReward(0);
            tbRewardStatisticsDto.setDayReward(0);
            tbRewardStatisticsDtos.add(tbRewardStatisticsDto);
        }
        Map<String,Object> map = new HashMap<>();
        map.put("ids",ids);
        //查询总打赏记录
        List<TbRewardRecord> tbRewardRecordTotal = tbRewardRecordService.findByDate(map);
        //循环添加
        for (TbRewardStatisticsDto tbRewardStatisticsDto:tbRewardStatisticsDtos){
            for (TbRewardRecord tbRewardRecord:tbRewardRecordTotal){
                if (tbRewardRecord.getBookId().equals(tbRewardStatisticsDto.getBookId())){
                    tbRewardStatisticsDto.setTotalReward(tbRewardRecord.getSum());
                }
            }
        }
        //查询年打赏记录
        map.put("rewardYear",year);
        List<TbRewardRecord> tbRewardRecordYear = tbRewardRecordService.findByDate(map);
        //循环添加
        for (TbRewardStatisticsDto tbRewardStatisticsDto:tbRewardStatisticsDtos){
            for (TbRewardRecord tbRewardRecord:tbRewardRecordYear){
                if (tbRewardRecord.getBookId().equals(tbRewardStatisticsDto.getBookId())){
                    tbRewardStatisticsDto.setYearReward(tbRewardRecord.getSum());
                }
            }
        }
        //查询月打赏记录
        map.put("rewardMonth",month);
        List<TbRewardRecord> tbRewardRecordMonth = tbRewardRecordService.findByDate(map);
        //循环添加
        for (TbRewardStatisticsDto tbRewardStatisticsDto:tbRewardStatisticsDtos){
            for (TbRewardRecord tbRewardRecord:tbRewardRecordMonth){
                if (tbRewardRecord.getBookId().equals(tbRewardStatisticsDto.getBookId())){
                    tbRewardStatisticsDto.setMonthReward(tbRewardRecord.getSum());
                }
            }
        }
        //查询日打赏
        map.put("rewardDay",day);
        List<TbRewardRecord> tbRewardRecordDay = tbRewardRecordService.findByDate(map);
        //循环添加
        for (TbRewardStatisticsDto tbRewardStatisticsDto:tbRewardStatisticsDtos){
            for (TbRewardRecord tbRewardRecord:tbRewardRecordDay){
                if (tbRewardRecord.getBookId().equals(tbRewardStatisticsDto.getBookId())){
                    tbRewardStatisticsDto.setDayReward(tbRewardRecord.getSum());
                }
            }
        }
        PageUtils pageUtil = new PageUtils(tbRewardStatisticsDtos, total, query.getLimit(), query.getPage());
        return R.ok().put("page",pageUtil);
    }


    /*
    * 根据时间查询销售统计
    */
    @RequestMapping("getSaleStatistics")
    public R saleStatistics(@RequestParam Map<String, Object> params){
        String date = String.valueOf(params.get("date"));
        String[] flag = date.split(":");
        String year = flag[0];
        String month = flag[1];
        String day = flag[2];
        params.remove("date");
        params.put("sell","1");
        //注入图书查询列表数据
        Query query = new Query(params);
        //查询出图书
        List<TdBook> tdBooks = tdBookService.queryList(query);
        int total = tdBookService.queryTotal(query);
        //初始化一个数据传输集合
        List<TbSaleStatisticsDto> tbSaleStatisticsDtos = new ArrayList<>();
        //取出图书id集合
        Integer[] ids = new Integer[tdBooks.size()];
        for (int i = 0; i < tdBooks.size(); i++) {
            ids[i] = tdBooks.get(i).getId();
        }
        //传入数据
        for (TdBook tdBook:tdBooks){
            TbSaleStatisticsDto tbSaleStatisticsDto = new TbSaleStatisticsDto();
            tbSaleStatisticsDto.setBookId(tdBook.getId());
            tbSaleStatisticsDto.setBookName(tdBook.getBookname());
            tbSaleStatisticsDto.setTotalSale(0);
            tbSaleStatisticsDto.setYearSale(0);
            tbSaleStatisticsDto.setMonthSale(0);
            tbSaleStatisticsDto.setDaySale(0);
            tbSaleStatisticsDtos.add(tbSaleStatisticsDto);
        }
        //查询总销售额
        Map<String,Object> map = new HashMap<>();
        map.put("ids",ids);
        List<TdOrder> tdOrderTotal= tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderTotal){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setTotalSale(tdOrder.getSum());
                }
            }
        }
        //查询年销售额
        map.put("year",year);
        List<TdOrder> tdOrderYear = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderYear){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setYearSale(tdOrder.getSum());
                }
            }
        }
        //查询月销售额
        map.put("month",month);
        List<TdOrder> tdOrderMonth = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderMonth){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setMonthSale(tdOrder.getSum());
                }
            }
        }
        //查询日销售额
        map.put("day",day);
        List<TdOrder> tdOrderDay = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderDay){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setDaySale(tdOrder.getSum());
                }
            }
        }
        PageUtils pageUtil = new PageUtils(tbSaleStatisticsDtos, total, query.getLimit(), query.getPage());
        return R.ok().put("page",pageUtil);
    }

    /**
     * 查询当月所有书籍销售统计
     * @return
     */
    @RequestMapping("/sameMonth")
    @RequiresPermissions("sys:statis:sameMonth")
    public R sameMonth(@RequestParam Map<String, Object> params){
        //查询列表数据
        Query query = new Query(params);
        Page<BookStatisticsDto> pageUtil = new Page<BookStatisticsDto>(query.getPage(), query.getLimit());
        Page<BookStatisticsDto> page = tdOrderService.sameMonth(pageUtil,query);
        return R.ok().put("page", page);
    }


    /**
     * 查询当月所有书籍销售统计
     * @return
     */
    @RequestMapping("/sameMonth2")
    @RequiresPermissions("sys:statis:sameMonth2")
    public R sameMonth2(@RequestParam Map<String, Object> params){
        try {
            String time = params.get("time").toString();
            if(!time.equals("")|| time != null){
                StringBuffer sb = new StringBuffer(time);
                sb.append("-01");
                time = sb.toString();
                params.put("time",time);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //查询列表数据
            Query query = new Query(params);
            Page<BookStatisticsDto> pageUtil = new Page<BookStatisticsDto>(query.getPage(), query.getLimit());
            Page<BookStatisticsDto> page = tdOrderService.sameMonth2(pageUtil,query);
            return R.ok().put("page", page);
        }
    }


    /**
     * 查询所有书籍销售统计
     * @return
     */
    @RequestMapping("/allStatistics")
    @RequiresPermissions("sys:statis:allStatistics")
    public R allStatistics(@RequestParam Map<String, Object> params){
        //查询列表数据
        Query query = new Query(params);
        query.put("rewardMoney","1");
        Page<BookStatisticsDto> pageUtil = new Page<BookStatisticsDto>(query.getPage(), query.getLimit());
        Page<BookStatisticsDto> page = tdOrderService.allStatistics(pageUtil,query);
        return R.ok().put("page", page);
    }

    /*
    * 根据时间找寻出销量的数据组装集合
    */
    public List<TbSaleStatisticsDto> getByDate(String date){
        String[] flag = date.split(":");
        String year = flag[0];
        String month = flag[1];
        String day = flag[2];
        //查询出所有图书，并按照销量降序排列
        List<TdBook> tdBooks = tdBookService.findAllOrderBySubscibe();
        //初始化一个数据传输集合
        List<TbSaleStatisticsDto> tbSaleStatisticsDtos = new ArrayList<>();
        //取出图书id集合
        Integer[] ids = new Integer[tdBooks.size()];
        for (int i = 0; i < tdBooks.size(); i++) {
            ids[i] = tdBooks.get(i).getId();
        }
        //传入数据
        for (TdBook tdBook:tdBooks){
            TbSaleStatisticsDto tbSaleStatisticsDto = new TbSaleStatisticsDto();
            tbSaleStatisticsDto.setBookId(tdBook.getId());
            tbSaleStatisticsDto.setBookName(tdBook.getBookname());
            tbSaleStatisticsDto.setTotalSale(0);
            tbSaleStatisticsDto.setYearSale(0);
            tbSaleStatisticsDto.setMonthSale(0);
            tbSaleStatisticsDto.setDaySale(0);
            tbSaleStatisticsDtos.add(tbSaleStatisticsDto);
        }
        //查询总销售额
        Map<String,Object> map = new HashMap<>();
        map.put("ids",ids);
        List<TdOrder> tdOrderTotal= tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderTotal){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setTotalSale(tdOrder.getSum());
                }
            }
        }
        //查询年销售额
        map.put("year",year);
        List<TdOrder> tdOrderYear = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderYear){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setYearSale(tdOrder.getSum());
                }
            }
        }
        //查询月销售额
        map.put("month",month);
        List<TdOrder> tdOrderMonth = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderMonth){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setMonthSale(tdOrder.getSum());
                }
            }
        }
        //查询日销售额
        map.put("day",day);
        List<TdOrder> tdOrderDay = tdOrderService.findByDate(map);
        //循环添加
        for (TbSaleStatisticsDto tbSaleStatisticsDto:tbSaleStatisticsDtos){
            for (TdOrder tdOrder:tdOrderDay){
                if (tbSaleStatisticsDto.getBookId().equals(tdOrder.getBookid())){
                    tbSaleStatisticsDto.setDaySale(tdOrder.getSum());
                }
            }
        }
        return tbSaleStatisticsDtos;
    }

    /*
    * 销量统计的Excel导出
    */
    @AuthIgnore2
    @RequestMapping("/educe")
    public void educe(HttpServletResponse response,String date) throws IOException {
        //根据查询出所有的销售数据集合
        List<TbSaleStatisticsDto> tbSaleStatisticsDtos = getByDate(date);
        //导出文件路径
        String basePath = "/file/";
        SimpleDateFormat dateFormat = new SimpleDateFormat("YYYYMMDDhhmmss");
        String now = dateFormat.format(new Date());
        //文件名
        String exportFileName = "销售统计-"+date.replace(":","")+"-"+now+".xls";
        String[] cellTitle = {"序号","图书名称","总销售额","年销售额","月销售额","日销售额"};
        // 声明一个工作薄
        XSSFWorkbook workBook = null;
        workBook = new XSSFWorkbook();
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
        XSSFSheet sheet = workBook.createSheet();
        workBook.setSheetName(0,"销售数据");
        // 创建表格标题行 第一行
        XSSFRow titleRow = sheet.createRow(0);
        for(int i=0;i<cellTitle.length;i++){
            titleRow.createCell(i).setCellValue(cellTitle[i]);
        }
        //插入需导出的数据
        for(int i=0;i<tbSaleStatisticsDtos.size();i++){
            XSSFRow row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(tbSaleStatisticsDtos.get(i).getBookName());
            row.createCell(2).setCellValue(getDoubleString(new BigDecimal(Double.valueOf(tbSaleStatisticsDtos.get(i).getTotalSale())/100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue())+"蝉币");
            row.createCell(3).setCellValue(getDoubleString(new BigDecimal(Double.valueOf(tbSaleStatisticsDtos.get(i).getYearSale())/100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue())+"蝉币");
            row.createCell(4).setCellValue(getDoubleString(new BigDecimal(Double.valueOf(tbSaleStatisticsDtos.get(i).getMonthSale())/100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue())+"蝉币");
            row.createCell(5).setCellValue(getDoubleString(new BigDecimal(Double.valueOf(tbSaleStatisticsDtos.get(i).getDaySale())/100).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue())+"蝉币");
        }
        setResponseHeader(response,exportFileName);
        ServletOutputStream outputStream = response.getOutputStream();
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

     /*
     * 如果是小数，保留两位，非小数，保留整数
     * @param number
     */
    public static String getDoubleString(double number) {
        String numberStr;
        if (((int) number * 1000) == (int) (number * 1000)) {
            //如果是一个整数
            numberStr = String.valueOf((int) number);
        } else {
            DecimalFormat df = new DecimalFormat("######0.00");
            numberStr = df.format(number);
        }
        return numberStr;
    }

    @RequestMapping("/export")
    public R export(@RequestParam String path, HttpServletRequest request, HttpServletResponse response){
        File file =new File(path);
        try {

            response.setCharacterEncoding("UTF-8");
            response.setContentType("multipart/form-data");
            String fileName="销售";//可以中文
            response.setHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(), "ISO-8859-1"));
            //打开本地文件流
            InputStream inputStream = new FileInputStream(path);
            //激活下载操作
            OutputStream os = response.getOutputStream();
            //循环写入输出流
            byte[] b = new byte[2048];
            int length;
            while ((length = inputStream.read(b)) > 0) {
                os.write(b, 0, length);
            }
            // 这里主要关闭。
            os.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return R.ok();
    }

    public static void main(String args[]){
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String date = dateFormat.format(new Date());
        date = date.replace("-",":");
        //根据查询出所有的销售数据集合
        TbSaleStatisticsDto tbSaleStatisticsDto = new TbSaleStatisticsDto();
        tbSaleStatisticsDto.setTotalSale(7717738);
        BigDecimal bg = new BigDecimal(Double.valueOf(tbSaleStatisticsDto.getTotalSale())/100);
        Double fontSize = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        String arr = getDoubleString(fontSize);
        System.out.println(arr);
    }
}
